package com.networkviwer.utils;

import java.sql.*;
import java.util.Map;

/**
 * @version V1.0
 * @Title:
 * @Package
 * @Description:
 * @author: runoob
 * @date:
 */
public class NetstatsDatasourceUtil {

    static public Connection getConnection() throws ClassNotFoundException, SQLException {
        String url = "jdbc:h2:./database;MV_STORE=FALSE;MVCC=FALSE;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0;DB_CLOSE_ON_EXIT=FALSE";
        String driverClass = "org.h2.Driver";
        Class.forName(driverClass);
        return DriverManager.getConnection(url);
    }

    static public void createTable(Statement statement) throws SQLException {
        statement.execute("DROP TABLE IF EXISTS netstats");
        statement.execute("create table netstats(" +
                "id int(11) primary key auto_increment," +
                "protocol varchar(3)," +
                "local_address varchar(32)," +
                "external_address varchar(32)," +
                "status varchar(20)," +
                "pid int(11))"
        );
    }

    static public void insert(Statement statement, Map<String,String> valueMap) throws SQLException {
        // 添加数据
        statement.executeUpdate("insert into netstats(protocol,local_address,external_address,status,pid) values(" +
                "'" + valueMap.get("protocol") + "'," +
                "'" + valueMap.get("local_address") + "'," +
                "'" + valueMap.get("external_address") + "'," +
                "'" + valueMap.get("status") + "'," +
                "" + valueMap.get("pid") + ")");
    }

    static public ResultSet getAllData(Statement statement) throws SQLException {
        return statement.executeQuery("select * from netstats");
    }

    static public void deleteAllData(Statement statement) throws SQLException {
        statement.executeUpdate("delete from netstats");
    }

    static public ResultSet getData(Statement statement, Map<String,String> valueMap) throws SQLException {
        String sql = "select * from netstats where 1=1 ";
        if (valueMap.containsKey("protocol")){
            sql += "and protocol like '%" + valueMap.get("protocol") + "%'";
        }
        if (valueMap.containsKey("local_address")){
            sql += "and local_address like '%" + valueMap.get("local_address") + "%'";
        }
        if (valueMap.containsKey("external_address")){
            sql += "and external_address like '%" + valueMap.get("external_address") + "%'";
        }
        if (valueMap.containsKey("status")){
            sql += "and status like '%" + valueMap.get("status") + "%'";
        }
        if (valueMap.containsKey("pid")){
            sql += "and pid like '%" + valueMap.get("pid") + "%'";
        }
        return statement.executeQuery(sql);
    }

}
